﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SapB1WebPOS.Utils;
using System.Data.SqlClient;
using System.Data;
using SapB1WebPOS.BLL;

namespace SapB1WebPOS.DAL
{
    public class SAPConnection
    {
        private SqlConnection sConnSAP;
        private SAPbobsCOM.Company oCompany;

        public string setConnectionStringDB(string sapUserName, string sapPassword, string companyDB)
        {
            try
            {
                sapUserName = System.Configuration.ConfigurationManager.AppSettings["SapUser"].ToString();
                sapPassword = System.Configuration.ConfigurationManager.AppSettings["SapPassword"].ToString();

                string SQLUser = null;
                string SQLPwd = null;
                string SQLServer = null;
                string LicenseServer = null;
                string SQLVersion = null;
                string strConnect = string.Empty;
                string sCon = string.Empty;
                string SQLType = string.Empty;
                string[] connectStringArr;
                string sErrMsg = string.Empty;
                strConnect = "DBConnect";
                sCon = System.Configuration.ConfigurationManager.AppSettings.Get(strConnect);
                connectStringArr = sCon.Split(';');

                if (connectStringArr.Length > 0)
                {
                    SQLServer = connectStringArr[0].ToString();
                    SQLUser = connectStringArr[1].ToString();
                    SQLPwd = connectStringArr[2].ToString();
                    LicenseServer = connectStringArr[3].ToString();
                    SQLVersion = connectStringArr[4].ToString();

                    // Select Company
                    sCon = "server= " + SQLServer + ";database=" + companyDB + " ;uid=" + SQLUser + "; pwd=" + SQLPwd + ";";
                    sConnSAP = new SqlConnection(sCon);

                    oCompany = new SAPbobsCOM.Company();
                    oCompany.CompanyDB = companyDB;
                    oCompany.UserName = sapUserName;
                    oCompany.Password = sapPassword;
                    oCompany.Server = SQLServer;
                    oCompany.DbUserName = SQLUser;
                    oCompany.DbPassword = SQLPwd;
                    oCompany.LicenseServer = LicenseServer;
                    if (SQLVersion == "2008")
                    {
                        oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008;
                    }
                    else if (SQLVersion == "2012")
                    {
                        oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012;
                    }
                    else if (SQLVersion == "2005")
                    {
                        oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2005;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return ex.Message;
            }
            return string.Empty;
        }
        public string connectDBForLogin()
        {
            try
            {
                string sErrMsg = string.Empty;
                int connectOk = 0;
                if (oCompany.Connected == false)
                {
                    if (oCompany.Connect() != 0)
                    {
                        oCompany.GetLastError(out connectOk, out sErrMsg);
                        Log.WriteError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, Utils.Utils.GetCurrentMethod(), "SAPError", sErrMsg);
                        return sErrMsg;
                    }

                    return string.Empty;
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
            }
            
            return string.Empty;
        }
        public SAPbobsCOM.Company connectDB()
        {
            try
            {
                string sErrMsg = string.Empty;
                int connectOk = 0;
                if (oCompany.Connected == false)
                {
                    if (oCompany.Connect() != 0)
                    {
                        oCompany.GetLastError(out connectOk, out sErrMsg);
                        Log.WriteError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, Utils.Utils.GetCurrentMethod(), "SAPError", sErrMsg);
                        return null;
                    }
                    return oCompany;
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
            }
            return null;
        }
        public double GetSAPItemPrice(string sapUserName, string sapPassword, string companyDB, string cardCode, string itemCode, float amount, DateTime refDate)
        {
            try
            {
                sapUserName = System.Configuration.ConfigurationManager.AppSettings["SapUser"].ToString();
                sapPassword = System.Configuration.ConfigurationManager.AppSettings["SapPassword"].ToString();

                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassword, companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    SAPbobsCOM.SBObob vObj = default(SAPbobsCOM.SBObob);
                    SAPbobsCOM.Recordset rs = default(SAPbobsCOM.Recordset);

                    vObj = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge);
                    rs = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                    rs = vObj.GetItemPrice(cardCode, itemCode, amount, refDate);
                    if (rs.RecordCount > 0)
                    {
                        return rs.Fields.Item(0).Value;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return 0;
            }

            return 0;
        }
        public DataTable CreateMarketingDocument(string sapUserName, string sapPassword, string companyDB, string strXml, string docType, string key, bool isUpdate)
        {
            sapUserName = System.Configuration.ConfigurationManager.AppSettings["SapUser"].ToString();
            sapPassword = System.Configuration.ConfigurationManager.AppSettings["SapPassword"].ToString();

            SAPConnection connect = new SAPConnection();
            SAPbobsCOM.Company oCompany = default(SAPbobsCOM.Company);
            int lErrCode = 0;
            string sErrMsg = string.Empty;
            try
            {
                dynamic oDocment = null;
                switch (docType)
                {
                    case "30":
                        oDocment = (SAPbobsCOM.JournalEntries)oDocment;
                        break;
                    case "97":
                        oDocment = (SAPbobsCOM.SalesOpportunities)oDocment;
                        break;
                    case "191":
                        oDocment = (SAPbobsCOM.ServiceCalls)oDocment;
                        break;
                    case "33":
                        oDocment = (SAPbobsCOM.Contacts)oDocment;
                        break;
                    case "221":
                        oDocment = (SAPbobsCOM.Attachments2)oDocment;
                        break;
                    case "2":
                        oDocment = (SAPbobsCOM.BusinessPartners)oDocment;
                        break;
                    case "28":
                        oDocment = (SAPbobsCOM.IJournalVouchers)oDocment;
                        break;
                    default:
                        oDocment = (SAPbobsCOM.Documents)oDocment;
                        break;
                }

                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassword, companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    oCompany.XMLAsString = true;
                    oDocment = oCompany.GetBusinessObjectFromXML(strXml, 0);
                    if (isUpdate)
                    {
                        if (oDocment.GetByKey(key))
                        {
                            oDocment.Browser.ReadXML(strXml, 0);
                            lErrCode = oDocment.Update();
                        }
                        else
                        {
                            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                        }
                    }
                    else
                    {
                        lErrCode = oDocment.Add();
                    }

                    if (lErrCode != 0)
                    {
                        oCompany.GetLastError(out lErrCode, out  sErrMsg);
                        return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                    }
                    else
                    {
                        if (!isUpdate)
                        {
                            string newDocEntry = oCompany.GetNewObjectKey();
                            return Utils.Utils.ReturnMessage(lErrCode, newDocEntry);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return Utils.Utils.ReturnMessage(lErrCode, ex.Message);
            }
            
            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
        }
        public DataTable CreateMarketingDocumentForTransaction(SAPbobsCOM.Company oCompany, string strXml, string docType, string key, bool isUpdate)
        {
            int lErrCode = 0;
            string sErrMsg = string.Empty;
            try
            {
                dynamic oDocment = null;
                switch (docType)
                {
                    case "30":
                        oDocment = (SAPbobsCOM.JournalEntries)oDocment;
                        break;
                    case "97":
                        oDocment = (SAPbobsCOM.SalesOpportunities)oDocment;
                        break;
                    case "191":
                        oDocment = (SAPbobsCOM.ServiceCalls)oDocment;
                        break;
                    case "33":
                        oDocment = (SAPbobsCOM.Contacts)oDocment;
                        break;
                    case "221":
                        oDocment = (SAPbobsCOM.Attachments2)oDocment;
                        break;
                    case "2":
                        oDocment = (SAPbobsCOM.BusinessPartners)oDocment;
                        break;
                    case "28":
                        oDocment = (SAPbobsCOM.IJournalVouchers)oDocment;
                        break;
                    default:
                        oDocment = (SAPbobsCOM.Documents)oDocment;
                        break;
                }

                if (oCompany != null && oCompany.Connected)
                {
                    oCompany.XMLAsString = true;
                    oDocment = oCompany.GetBusinessObjectFromXML(strXml, 0);
                    if (isUpdate)
                    {
                        if (oDocment.GetByKey(key))
                        {
                            oDocment.Browser.ReadXML(strXml, 0);
                            lErrCode = oDocment.Update();
                        }
                        else
                        {
                            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                        }
                    }
                    else
                    {
                        lErrCode = oDocment.Add();
                    }

                    if (lErrCode != 0)
                    {
                        oCompany.GetLastError(out lErrCode, out  sErrMsg);
                        return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                    }
                    else
                    {
                        if (!isUpdate)
                        {
                            return Utils.Utils.ReturnMessage(lErrCode, "");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return Utils.Utils.ReturnMessage(lErrCode, ex.Message);
            }
            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
        }
        public DataTable CloseCancelDocument(string sapUserName, string sapPassword, string companyDB, string docType, string key, bool isCancel)
        {
            sapUserName = System.Configuration.ConfigurationManager.AppSettings["SapUser"].ToString();
            sapPassword = System.Configuration.ConfigurationManager.AppSettings["SapPassword"].ToString();

            SAPConnection connect = new SAPConnection();
            SAPbobsCOM.Company oCompany = default(SAPbobsCOM.Company);
            int lErrCode = 0;
            string sErrMsg = string.Empty;
            try
            {
                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassword, companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    dynamic oDocment = null;

                    switch (docType)
                    {
                        case "30":
                            oDocment = (SAPbobsCOM.JournalEntries)oDocment;
                            break;
                        case "97":
                            oDocment = (SAPbobsCOM.SalesOpportunities)oDocment;
                            break;
                        case "191":
                            oDocment = (SAPbobsCOM.ServiceCalls)oDocment;
                            break;
                        case "33":
                            oDocment = (SAPbobsCOM.Contacts)oDocment;
                            break;
                        case "221":
                            oDocment = (SAPbobsCOM.Attachments2)oDocment;
                            break;
                        case "2":
                            oDocment = (SAPbobsCOM.BusinessPartners)oDocment;
                            break;
                        case "28":
                            oDocment = (SAPbobsCOM.IJournalVouchers)oDocment;
                            break;
                        case "23":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oQuotations);
                            break;
                        case "17":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
                            break;
                        case "15":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes);
                            break;
                        case "13":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices);
                            break;
                        case "18":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseInvoices);
                            break;
                        case "24":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments);
                            break;
                        case "22":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders);
                            break;
                        case "20":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes);
                            break;
                        case "67":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer);
                            break;
                        case "1250000001":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryTransferRequest);
                            break;
                        case "112":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDrafts);
                            break;
                        default:
                            oDocment = (SAPbobsCOM.Documents)oDocment;
                            break;
                    }
                    if (oDocment.GetByKey(key))
                    {
                        if (isCancel)
                        {
                            if (docType == ConvertDocType.Get2String(DocType.GOODS_RECEIPT_PO)
                                || docType == ConvertDocType.Get2String(DocType.INCOMING)
                                || docType == ConvertDocType.Get2String(DocType.AR_INVOICE))
                            {
                                SAPbobsCOM.Documents ocanclleddoc;
                                ocanclleddoc = oDocment.CreateCancellationDocument();
                                lErrCode = ocanclleddoc.Add();
                            }
                            else
                            {
                                lErrCode = oDocment.Cancel();
                            }
                        }
                        else
                        {
                            lErrCode = oDocment.Close();
                        }
                        if (lErrCode != 0)
                        {
                            oCompany.GetLastError(out lErrCode, out  sErrMsg);
                            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                        }
                        else
                        {
                            return Utils.Utils.ReturnMessage(lErrCode, key);
                        }
                    }
                    else
                    {
                        return Utils.Utils.ReturnMessage(lErrCode, "Can not found document.");
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return Utils.Utils.ReturnMessage(lErrCode, ex.Message);
            }
           
            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
        }
        public DataTable DeleteMarketingDocument(string sapUserName, string sapPassword, string companyDB, DataTable tblLine, string docType, string key)
        {
            sapUserName = System.Configuration.ConfigurationManager.AppSettings["SapUser"].ToString();
            sapPassword = System.Configuration.ConfigurationManager.AppSettings["SapPassword"].ToString();

            SAPConnection connect = new SAPConnection();
            SAPbobsCOM.Company oCompany = default(SAPbobsCOM.Company);
            int lErrCode = 0;
            string sErrMsg = string.Empty;
            string tableLineName = string.Empty;
            try
            {
                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassword, companyDB);
                oCompany = this.connectDB();
                if (oCompany != null && oCompany.Connected)
                {
                    dynamic oDocment = null;

                    switch (docType)
                    {
                        case "30":
                            oDocment = (SAPbobsCOM.JournalEntries)oDocment;
                            break;
                        case "97":
                            oDocment = (SAPbobsCOM.SalesOpportunities)oDocment;
                            break;
                        case "191":
                            oDocment = (SAPbobsCOM.ServiceCalls)oDocment;
                            break;
                        case "33":
                            oDocment = (SAPbobsCOM.Contacts)oDocment;
                            break;
                        case "221":
                            oDocment = (SAPbobsCOM.Attachments2)oDocment;
                            break;
                        case "2":
                            oDocment = (SAPbobsCOM.BusinessPartners)oDocment;
                            break;
                        case "28":
                            oDocment = (SAPbobsCOM.IJournalVouchers)oDocment;
                            break;
                        case "23":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oQuotations);
                            tableLineName = "QUT1";
                            break;
                        case "17":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oOrders);
                            tableLineName = "RDR1";
                            break;
                        case "15":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDeliveryNotes);
                            break;
                        case "13":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices);
                            break;
                        case "18":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseInvoices);
                            break;
                        case "24":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments);
                            break;
                        case "22":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseOrders);
                            tableLineName = "POR1";
                            break;
                        case "20":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseDeliveryNotes);
                            tableLineName = "PDN1";
                            break;
                        case "67":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oStockTransfer);
                            break;
                        case "1250000001":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryTransferRequest);
                            break;
                        case "112":
                            oDocment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oDrafts);
                            break;
                        default:
                            oDocment = (SAPbobsCOM.Documents)oDocment;
                            break;
                    }
                    if (oDocment.GetByKey(key))
                    {
                        SAPbobsCOM.Recordset linesRs = default(SAPbobsCOM.Recordset);
                        linesRs = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                        linesRs.DoQuery(string.Format("SELECT LineNum FROM {0} WHERE DocEntry = {1} ORDER BY LineNum", tableLineName, key));
                        if (oDocment.GetByKey(key))
                        {
                            bool isDeleted = false;
                            int deletedCount = 0;
                            if (tblLine != null)
                            {
                                foreach (DataRow row in tblLine.Rows)
                                {
                                    int lineIndex = 0;
                                    linesRs.MoveFirst();
                                    lineIndex = lineIndex - deletedCount;
                                    while (linesRs.EoF == false)
                                    {
                                        if (linesRs.Fields.Item("LineNum").Value == int.Parse(row["LineNum"].ToString()))
                                        {
                                            isDeleted = true;
                                            break;
                                        }
                                        else
                                        {
                                            isDeleted = false;
                                        }
                                        lineIndex = lineIndex + 1;
                                        linesRs.MoveNext();
                                    }
                                    if (isDeleted)
                                    {
                                        oDocment.Lines.SetCurrentLine(lineIndex);
                                        oDocment.Lines.Delete();
                                        deletedCount = deletedCount + 1;
                                    }
                                }
                                lErrCode = oDocment.Update();
                                if (lErrCode != 0)
                                {
                                    oCompany.GetLastError(out lErrCode, out  sErrMsg);
                                    return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
                                }
                                else
                                {
                                    return Utils.Utils.ReturnMessage(lErrCode, key);
                                }
                            }
                        }
                        else
                        {
                            return Utils.Utils.ReturnMessage(-1, "Record not found!");
                        }
                    }
                    else
                    {
                        return Utils.Utils.ReturnMessage(-1, "Can not found document.");
                    }
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return Utils.Utils.ReturnMessage(-1, ex.Message);
            }
            
            return Utils.Utils.ReturnMessage(lErrCode, sErrMsg);
        }
        public string Pos2SAP(string sapUserName, string sapPassword, string companyDB, DataSet dsInput)
        {
            try
            {
                sapUserName = System.Configuration.ConfigurationManager.AppSettings["SapUser"].ToString();
                sapPassword = System.Configuration.ConfigurationManager.AppSettings["SapPassword"].ToString();

                string DocEntry_Invoice = string.Empty;
                string objType = string.Empty;
                string whsCode = string.Empty;
                string paymentCashAcc = string.Empty;
                string paymentTransferAcc = string.Empty;
                DateTime taxDate = new DateTime();
                double docTotal = 0;

                CommonBLL common = new CommonBLL();
                SAPConnection connect = new SAPConnection();
                SAPbobsCOM.Company oCompany = default(SAPbobsCOM.Company);
                string sErrMsg = string.Empty;
                string constr = null;
                constr = this.setConnectionStringDB(sapUserName, sapPassword, companyDB);
                oCompany = this.connectDB();

                // Check SAP connection
                if (oCompany == null || !oCompany.Connected)
                {
                    return "Can not connect to SAP B1.";
                }

                string xmlStr = "";
                DataSet ds = new DataSet();

                DataTable dtHeader = BuildTableHeader();
                DataTable dtLine = BuildTableLine();
                // Set Data
                foreach (DataRow rHeader in dsInput.Tables[0].Rows)
                {
                    objType = rHeader["ObjType"].ToString();
                    docTotal = double.Parse(rHeader["DocTotal"].ToString());
                    taxDate = DateTime.Parse(rHeader["TaxDate"].ToString());
                    InsertIntoHeader(dtHeader, rHeader);
                }
                foreach (DataRow rLine in dsInput.Tables[1].Rows)
                {
                    whsCode = rLine["WhsCode"].ToString();
                    InsertIntoLine(dtLine, rLine, companyDB);
                }
                DataSet dsPaymentAccount = null;
                dsPaymentAccount = common.GetPaymentAccount(companyDB, taxDate);


                if (dsPaymentAccount != null && dsPaymentAccount.Tables.Count > 0 && dsPaymentAccount.Tables[0].Rows.Count > 0)
                {
                    paymentCashAcc = dsPaymentAccount.Tables[0].Rows[0]["CashAccount"].ToString();
                    paymentTransferAcc = dsPaymentAccount.Tables[0].Rows[0]["BankTransferAccount"].ToString();
                }
                if (objType == "13")
                {
                    dtHeader.TableName = Utils.TableNameDefined.AR_INVOICE_HEADER;
                    dtLine.TableName = Utils.TableNameDefined.AR_INVOICE_LINE;

                    dtHeader.Columns.Remove("DocType");
                    dtHeader.Columns.Remove("DocDueDate");

                    dtLine.Columns.Remove("BaseLine");
                    dtLine.Columns.Remove("BaseEntry");
                    dtLine.Columns.Remove("BaseType");
                }
                else
                {
                    dtHeader.TableName = Utils.TableNameDefined.SALES_ORDER_HEADER;
                    dtLine.TableName = Utils.TableNameDefined.SALES_ORDER_LINE;
                }

                ds.Tables.Add(dtHeader.Copy());
                ds.Tables.Add(dtLine.Copy());

                xmlStr = Utils.DocumentXML.ToXMLStringFromDS(objType, ds);

                oCompany.StartTransaction();

                DataTable tbResult = CreateMarketingDocumentForTransaction(oCompany, xmlStr, objType, "", false);
                if (tbResult != null && tbResult.Rows.Count > 0)
                {
                    if ((int)tbResult.Rows[0]["ErrCode"] != 0)
                    {
                        //update failed
                        if (oCompany.InTransaction)
                        {
                            oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack);
                        }
                        return "Error: " + tbResult.Rows[0]["ErrMsg"].ToString();
                    }
                    else
                    {
                        //update ok
                        DocEntry_Invoice = oCompany.GetNewObjectKey();
                        DataTable dtORCT = BuildTableORCT();
                        DataTable dtRCT2 = BuildTableRCT2();
                        DataTable dtRCT3 = BuildTableRCT3();
                        if (objType == "13")
                        {
                            DataTable tbPayment = dsInput.Tables[2];

                            double cashAmount = 0, transferAmount = 0;

                            if (tbPayment != null && tbPayment.Rows.Count > 0)
                            {
                                DataRow[] rCash = tbPayment.Select("PaymentCode='CASH'");
                                if (rCash.Length > 0)
                                {
                                    cashAmount = double.Parse(rCash[0]["Amount"].ToString());
                                }
                                DataRow[] rTransfer = tbPayment.Select("PaymentCode='BT'");
                                if (rTransfer.Length > 0)
                                {
                                    transferAmount = double.Parse(rTransfer[0]["Amount"].ToString());
                                }
                            }

                            dtORCT = InsertIntoORCT(dtORCT, dsInput.Tables[0].Rows[0], whsCode, paymentCashAcc, paymentTransferAcc,cashAmount,transferAmount);

                            if (dtORCT.Rows.Count > 0)
                            {
                                dtRCT2 = InsertIntoRCT2(dtRCT2, DocEntry_Invoice, docTotal);
                                foreach (DataRow r in dsInput.Tables[2].Rows)
                                {
                                    if (r["PaymentCode"].ToString().ToUpper() != "CASH" &&
                                        r["PaymentCode"].ToString().ToUpper() != "BT" &&
                                        double.Parse(r["Amount"].ToString().ToUpper()) > 0)
                                    {
                                        dtRCT3 = InsertIntoRCT3(companyDB, dtRCT3, r);
                                    }
                                }

                                string paymentObjType = "";

                                paymentObjType = ConvertDocType.Get2String(DocType.INCOMING);

                                dtORCT.TableName = Utils.TableNameDefined.INCOMING_PAYMENT_HEADER;
                                dtRCT2.TableName = Utils.TableNameDefined.INCOMING_PAYMENT_LINE;
                                dtRCT3.TableName = Utils.TableNameDefined.INCOMING_PAYMENT_CC_LINE;

                                ds = new DataSet();
                                ds.Tables.Add(dtORCT);
                                ds.Tables.Add(dtRCT2);
                                if (dtRCT3.Rows.Count > 0)
                                {
                                    ds.Tables.Add(dtRCT3);
                                }

                                xmlStr = DocumentXML.ToXMLStringFromDS(paymentObjType, ds);

                                DataTable dtPayment = CreateMarketingDocumentForTransaction(oCompany, xmlStr, paymentObjType, "", false);
                                if (dtPayment != null && dtPayment.Rows.Count > 0)
                                {
                                    if ((int)dtPayment.Rows[0]["ErrCode"] != 0)
                                    {
                                        if (oCompany.InTransaction)
                                        {
                                            oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack);
                                        }
                                        return "Error: " + dtPayment.Rows[0]["ErrMsg"].ToString();
                                    }
                                    else
                                    {
                                        if (oCompany.InTransaction)
                                        {
                                            oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit);
                                            return DocEntry_Invoice;
                                        }
                                    }
                                }
                            }
                            else
                            {
                                if (oCompany.InTransaction)
                                {
                                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (oCompany.InTransaction)
                {
                    oCompany.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack);
                }
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                return ex.ToString();
            }
           
            return string.Empty;
        }
        private DataTable InsertIntoHeader(DataTable dt, DataRow dr)
        {
            DataRow drNew = dt.NewRow();
            drNew["DocDueDate"] = Convert.ToDateTime(dr["DocDueDate"]).ToString(Utils.Utils.SAP_FORMAT_DATE);
            drNew["DocDate"] = Convert.ToDateTime(dr["DocDate"]).ToString(Utils.Utils.SAP_FORMAT_DATE);
            drNew["TaxDate"] = Convert.ToDateTime(dr["TaxDate"]).ToString(Utils.Utils.SAP_FORMAT_DATE);
            drNew["CardCode"] = dr["CardCode"].ToString();
            drNew["CardName"] = dr["CardName"].ToString();
            drNew["Comments"] = dr["Comments"].ToString();
            drNew["DocEntry"] = dr["DocEntry"].ToString();
            drNew["ObjType"] = dr["ObjType"].ToString();
            drNew["DocTotal"] = dr["DocTotal"].ToString();
          

            dt.Rows.Add(drNew);
            return dt;
        }
        private DataTable InsertIntoLine(DataTable dt, DataRow dr, string companyDB)
        {
            if (dr["Quantity"].ToString() != "0")
            {
                DataRow drNew = dt.NewRow();
                drNew["LineNum"] = dr.Table.Rows.IndexOf(dr);
                drNew["ItemCode"] = dr["ItemCode"];
                drNew["Dscription"] = dr["Dscription"];
                drNew["Quantity"] = dr["Quantity"];
                drNew["WhsCode"] = dr["WhsCode"];
                drNew["DiscPrcnt"] = dr["DiscPrcnt"].ToString();
                drNew["PriceBefDi"] = dr["PriceBefDi"].ToString();
                drNew["VatGroup"] = dr["VatGroup"].ToString();
                drNew["VatPrcnt"] = dr["VatPrcnt"].ToString();
                drNew["VatSum"] = dr["VatSum"].ToString();
                drNew["SlpCode"] = dr["SlpCode"].ToString();

                drNew["BaseLine"] = dr["BaseLine"];
                drNew["BaseType"] = dr["BaseType"];
                drNew["BaseEntry"] = dr["BaseEntry"];

                dt.Rows.Add(drNew);
            }
            return dt;
        }
        private DataTable InsertIntoORCT(DataTable dt, DataRow dr, string WhsCode, string paymentCashAcc, string paymentTransferAcc, double cashAmount, double transferAmount)
        {
            DataRow drNew = dt.NewRow();
            drNew["DocDate"] = Convert.ToDateTime(dr["DocDate"]).ToString(Utils.Utils.SAP_FORMAT_DATE);
            drNew["DocDueDate"] = Convert.ToDateTime(dr["DocDate"]).ToString(Utils.Utils.SAP_FORMAT_DATE);
            drNew["TaxDate"] = Convert.ToDateTime(dr["TaxDate"]).ToString(Utils.Utils.SAP_FORMAT_DATE);
            drNew["CardCode"] = dr["CardCode"];
            drNew["CardName"] = dr["CardName"];
            drNew["CashSum"] = Math.Abs(cashAmount);
            drNew["CashAcct"] = paymentCashAcc;
            drNew["TrsfrSum"] = Math.Abs(transferAmount);
            drNew["TrsfrAcct"] = paymentTransferAcc;
            drNew["DocType"] = "C";
            drNew["CounterRef"] = WhsCode;
            dt.Rows.Add(drNew);
            return dt;
        }
        private DataTable InsertIntoRCT2(DataTable dt, string DocEntry_Invoice,double docTotal)
        {
            DataRow drNew = dt.NewRow();
            drNew["DocEntry"] = DocEntry_Invoice;
            drNew["InvType"] = "13";
            drNew["DocLine"] = 0;
            //drNew["SumApplied"] = docTotal;
            dt.Rows.Add(drNew);
            return dt;
        }
        private DataTable InsertIntoRCT3(string companyDB, DataTable dt, DataRow dr)
        {
            DataRow drNew = dt.NewRow();
            drNew["CreditCard"] = dr["PaymentCode"].ToString();
            drNew["CardValid"] = "99990101";
            drNew["CrCardNum"] = "999";
            drNew["CreditSum"] = dr["Amount"];
            drNew["VoucherNum"] = "1";
            drNew["CreditAcct"] = dr["AcctCode"].ToString();
            dt.Rows.Add(drNew);
            return dt;
        }
        private int GetCrCCodeByName(string companyDB, string CrCName)
        {
            int crCCode = 0;
            string strQuery = "";
            SQLConnection cn = new SQLConnection(companyDB);
            List<object> lstParamValue = new List<object>();
            lstParamValue.Add(CrCName);

            strQuery = "SELECT CreditCard FROM OCRC WHERE ISNULL(CardName,'')=@Param1";
            DataSet ds = cn.Excute_Query(strQuery, lstParamValue);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                crCCode = int.Parse(ds.Tables[0].Rows[0]["CreditCard"].ToString());
            }
            else
            {
                crCCode = 0;
            }

            return crCCode;
        }
        private int GetCreditCardGL(string companyDB, string CrCName)
        {
            int crCCode = 0;
            string strQuery = "";
            SQLConnection cn = new SQLConnection(companyDB);
            List<object> lstParamValue = new List<object>();
            lstParamValue.Add(CrCName);
            strQuery = "SELECT AcctCode FROM OCRC WHERE ISNULL(CardName,'')= @Param1";
            DataSet ds = cn.Excute_Query(strQuery, lstParamValue);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                crCCode = int.Parse(ds.Tables[0].Rows[0]["AcctCode"].ToString());
            }
            else
            {
                crCCode = 0;
            }
            return crCCode;
        }
        private DataTable BuildTableORCT()
        {
            //Incoming Payment
            DataTable dt = new DataTable();
            dt.Columns.Add("DocDate");
            dt.Columns.Add("DocDueDate");
            dt.Columns.Add("TaxDate");
            dt.Columns.Add("CardCode");
            dt.Columns.Add("CardName");
            dt.Columns.Add("CashSum");
            dt.Columns.Add("CashAcct");
            dt.Columns.Add("TrsfrAcct");
            dt.Columns.Add("TrsfrSum");
            dt.Columns.Add("DocType");
            dt.Columns.Add("CounterRef");
            return dt;
        }
        private DataTable BuildTableRCT2()
        {
            //Incoming Payment Detail
            DataTable dt = new DataTable();
            dt.Columns.Add("DocEntry");
            dt.Columns.Add("InvType");
            //dt.Columns.Add("SumApplied");
            dt.Columns.Add("DocLine");
            return dt;
        }
        private DataTable BuildTableRCT3()
        {
            //Incoming Payment Detail
            DataTable dt = new DataTable();
            dt.Columns.Add("CreditCard");
            dt.Columns.Add("CardValid");
            dt.Columns.Add("CrCardNum");
            dt.Columns.Add("CreditSum");
            dt.Columns.Add("VoucherNum");
            dt.Columns.Add("CreditAcct");
            return dt;
        }
        private DataTable BuildTableHeader()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("DocEntry");
            dt.Columns.Add("DocDueDate");
            dt.Columns.Add("TaxDate");
            dt.Columns.Add("DocDate");
            dt.Columns.Add("CardCode");
            dt.Columns.Add("CardName");
            dt.Columns.Add("ObjType");
            dt.Columns.Add("DocType");
            dt.Columns.Add("Comments");
            dt.Columns.Add("DocTotal");
            return dt;
        }
        private DataTable BuildTableLine()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ItemCode");
            dt.Columns.Add("Dscription");
            dt.Columns.Add("WhsCode");
            dt.Columns.Add("Quantity");
            dt.Columns.Add("LineNum");
            dt.Columns.Add("VatGroup");
            dt.Columns.Add("VatPrcnt");
            dt.Columns.Add("VatSum");
            dt.Columns.Add("PriceBefDi");
            dt.Columns.Add("unitMsr");
            dt.Columns.Add("DiscPrcnt");
            dt.Columns.Add("SlpCode");

            dt.Columns.Add("BaseLine");
            dt.Columns.Add("BaseType");
            dt.Columns.Add("BaseEntry");
            return dt;
        }
    }
}